


***Set paths
local dir "..."
local dropbox_data "..."
local dropbox_data_create "..."
local local_data "..."


tempfile tempdup 
save `tempdup'

bys date exec_time_grid isin: gen a0=_N 
tab isin if a0>1
drop a0 


import excel  "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z73W27") firstrow clear 
gen isin = "CA1350Z73W27"
tempfile temp1 
save `temp1'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z73Z57") firstrow clear
gen isin = "CA1350Z73Z57"
tempfile temp2
save `temp2'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z74D37") firstrow clear 
gen isin = "CA1350Z74D37"
tempfile temp3
save `temp3'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z74J07") firstrow clear
gen isin = "CA1350Z74J07"
tempfile temp4
save `temp4'
 
import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z74N19") firstrow clear
gen isin = "CA1350Z74N19"
tempfile temp5
save `temp5'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z74Q40") firstrow clear
gen isin = "CA1350Z74Q40"
tempfile temp6
save `temp6'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z74U51") firstrow clear
gen isin = "CA1350Z74U51"
tempfile temp7
save `temp7'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z74W18") firstrow clear
gen isin = "CA1350Z74W18"
tempfile temp8
save `temp8'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z75A88") firstrow clear
gen isin = "CA1350Z75A88"
tempfile temp9
save `temp9'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z75C45") firstrow clear
gen isin = "CA1350Z75C45"
tempfile temp10
save `temp10'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z75D28") firstrow clear
gen isin = "CA1350Z75D28"
tempfile temp11
save `temp11'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z75G58") firstrow clear
gen isin = "CA1350Z75G58"
tempfile temp12
save `temp12'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z75H32") firstrow clear
gen isin = "CA1350Z75H32"
tempfile temp13
save `temp13'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z75J97") firstrow clear
gen isin = "CA1350Z75J97"
tempfile temp14
save `temp14'

import excel "`dir'/book_of_duplicates_bonds.xlsx", sheet("CA1350Z75K60") firstrow clear
gen isin = "CA1350Z75K60"
tempfile temp15
save `temp15'


append using `temp14'
append using `temp13'
append using `temp12'
append using `temp11'
append using `temp10'
append using `temp9'
append using `temp8'
append using `temp7'
append using `temp6'
append using `temp5'
append using `temp4'
append using `temp3'
append using `temp2'
append using `temp1'

drop A F G H I J K L 

rename BidPriceRealtime bidprice_bl 
rename AskPriceRealtime askprice_bl 

rename BidYield bidyield_bl 
rename AskYield askyield_bl 
gen coupon_bl=.
drop if Date==.

*normalize 
replace bidyield_bl = bidyield_bl*100 // in bsp
replace askyield_bl = askyield_bl*100 // in bsp

gen double midyield_bl = (askyield_bl+bidyield_bl)/2
gen double midprice_bl = (askprice_bl+bidprice_bl)/2

*correct time 
gen double dateround = round(Date,1000*60*60) // round milli-seconds to minutes, else the ceiling does not work 
format dateround %tc
gen double exec_time_grid = 30*2 *60000 * ceil(dateround/(30*2*60 * 1000))
format exec_time_grid %tc

gen date = dofc(exec_time_grid)
format date %td

gen year = year(date)
drop if year<2016 | year>2019
drop year 

unique isin 

bys date exec_time_grid isin: gen a0=_N 
tab isin if a0>1
drop a0 

gen old =0

append using `tempdup'

bys date exec_time_grid isin: gen a0=_N 
tab isin if a0>1
drop a0 

